﻿using System;
using System.Collections.Generic;
using System.Text;
using Xeml.Sampling.Contracts;
using Xeml.Sampling;
using Mpi.Common.Collections;
using Xeml.Document;
using Mpi.Common;
using System.Data.SqlClient;
using System.Reflection;
using System.ComponentModel;
using System.Linq;

namespace SQLDataProviders 
{
    public class GmdDataProvider : IDataProvider
    {
        private string _UserName;
        private string _Password;
        private bool isAuthenticated;

        #region constructor
        /// <summary>
        /// The standard constructor
        /// </summary>
        public GmdDataProvider()
        {
            ConfigurationObject = new GmdDataProviderConfig();
            ConfigurationObject.PropertyChanged += (object sender, PropertyChangedEventArgs e) => { isAuthenticated = false; };
            isAuthenticated = false;
        }
        #endregion constructor

        #region interface IXemlComponent
        /// <summary>
        /// The author of the component.
        /// </summary>
        public string Author
        {
            get { return "Jan Hummel"; }
        }

        /// <summary>
        /// The name of the component.
        /// </summary>
        public string ComponentName
        {
            get { return "MS SQL Connector"; }
        }

        /// <summary>
        /// Creates a copy of the component
        /// </summary>
        /// <returns></returns>
        public Xeml.Document.Contracts.IXemlComponent Copy()
        {
            return new GmdDataProvider();
        }

        /// <summary>
        /// A short description about the aim of the component.
        /// </summary>
        public string Description
        {
            get { return "A data provider for metabolite profile data from the Golm Metabolome Database (GMD, http://gmd.mpimp-golm.mpg.de/)."; }
        }

        /// <summary>
        /// The name of the publisher of this component.
        /// </summary>
        public string Publisher
        {
            get { return "Max Planck Institute of Molecular Plant Physiology"; }
        }

        /// <summary>
        /// The universal identifiere for this handler
        /// </summary>
        public string Uri
        {
            get { return "http://gmd.mpimp-golm.mpg.de/xeml/2011/"; }
        }

        public Version Version
        {
            get { return Assembly.GetExecutingAssembly().GetName().Version; }
        }
        #endregion interface IXemlComponent

        #region interface IDataProvider
        /// <summary>
        /// An event handler for requesting authentication information.
        /// </summary>
        public event EventHandler<AuthenticationRequestEventArgs> AuthenticationRequest;

        public event EventHandler<XemlFilePathRequestEventArgs> XemlFilePathRequest;


        /// <summary>
        /// Maps samples automatically
        /// </summary>
        /// <param name="sidmappings">The samples to map</param>
        /// <returns>Indicates whether or not the mapping was successfull</returns>
        public IEnumerable<SidMapping> AutoMap(Guid expId, string expName, IEnumerable<int> samples)
        {
            throw new NotImplementedException();

            //AuthenticationRequestEventArgs args = CheckAuthentication();
            //string workingConnstring = BuildWorkingConnectionString(args);

            //using (SqlConnection sqlconn = new SqlConnection(workingConnstring))
            //{
            //    SqlCommand cmd = new SqlCommand();
            //    cmd.CommandType = System.Data.CommandType.Text;
            //    cmd.Connection = sqlconn;
            //    cmd.CommandText = "select * from [dbo].[FreshWeights] where Experiment_Fk = @experiment and Sample = @sample";
            //    cmd.Parameters.Add("@experiment", System.Data.SqlDbType.NVarChar);
            //    cmd.Parameters.Add("@sample", System.Data.SqlDbType.Int);
            //    sqlconn.Open();
            //    try
            //    {
            //        foreach (var item in sidmappings)


            //        {

            //            cmd.Parameters["@experiment"].Value = expName;
            //            cmd.Parameters["@sample"].Value = item.SampleId;
            //            using (SqlDataReader reader = cmd.ExecuteReader())
            //            {
            //                if (reader.HasRows)
            //                {
            //                    item.ForeignKeyMap.Add("Experiment", expName);
            //                    item.ForeignKeyMap.Add("Sample", item.SampleId.ToString());
            //                }
            //                reader.Close();
            //            }
            //        }
            //    }
            //    finally
            //    {
            //        sqlconn.Close();
            //    }
            //}
        }
        /// <summary>
        /// A collection of properties are required to customize the compilation
        /// </summary>
        public INotifyPropertyChanged ConfigurationObject { get; private set; }

        public IEnumerable<string> FilterKeyValues(string key, Dictionary<string, string> filter)
        {
            bool success;

            using (SqlCommand cmd = new SqlCommand())
            using (cmd.Connection = new SqlConnection(GetConnectionString(out success)))
            {
                if (success)
                {
                    switch (key)
                    {
                        case "Experiment":
                            if (filter != null && filter.ContainsKey("Chromatogram") && !string.IsNullOrEmpty(filter["Chromatogram"]))
                            {
                                cmd.CommandText = @"
                                SELECT distinct t.name FROM GC_Chromatogram c WITH(NOLOCK)
	                                INNER JOIN tf.IntensityValue i WITH(NOLOCK) ON c.id = i.FK_chromatogram 
	                                INNER JOIN tf.TagList t WITH(NOLOCK) ON i.FK_TagList = t.id
	                                WHERE c.name = @chromatogram
	                                ORDER BY t.name";
                                cmd.Parameters.AddWithValue("@chromatogram", filter["Chromatogram"]);
                            }
                            else
                            {
                                cmd.CommandText = "select name from tf.TagList order by name";
                            }
                            break;

                        case "Chromatogram":

                            if (filter != null && filter.ContainsKey("Experiment") && !string.IsNullOrEmpty(filter["Experiment"]))
                            {
                                cmd.CommandText = @"
                                    SELECT distinct c.name FROM GC_Chromatogram c WITH(NOLOCK) 
	                                    INNER JOIN tf.IntensityValue i WITH(NOLOCK) ON c.id = i.FK_chromatogram
	                                    INNER JOIN tf.TagList t WITH(NOLOCK) ON i.FK_TagList = t.id
	                                    WHERE t.name = @experiment
	                                    ORDER BY c.name";
                                cmd.Parameters.AddWithValue("@experiment", filter["Experiment"]);
                            }
                            else
                            {
                                cmd.CommandText = "select name from dbo.GC_Chromatogram order by name";
                            }
                            break;
                        default:
                            throw new ArgumentException("key");
                    }
                    cmd.CommandType = System.Data.CommandType.Text;

                    try
                    {
                        cmd.Connection.Open();
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                yield return reader.GetString(0);
                            }
                        }
                    }
                    finally
                    {
                        cmd.Connection.Close();
                    }
                }
            }
        }

        public IEnumerable<string> ListForeignKey()
        {
            yield return "Experiment";
            yield return "Chromatogram";
            yield break;
        }

        /// <summary>
        /// An optional logo for the provider
        /// </summary>
        public System.Drawing.Image Logo
        {
            get { return SQLDataProviders.Properties.Resources.GmdLogo; }
        }

        /// <summary>
        /// Collects the the sample data for specific list of sample identfiere. 
        /// </summary>
        /// <param name="samples">The list of sample identifiere.</param>
        public KeyKeyValueCollection<string, int, object> QueryData(List<SidMapping> samples)
        {
            KeyKeyValueCollection<string, int, object> ret = new KeyKeyValueCollection<string, int, object>();
            //metabolite, sampleid in xeml, messwert

            bool success;

            using (SqlCommand cmd = new SqlCommand())
            using (cmd.Connection = new SqlConnection(GetConnectionString(out success)))
            {
                if (success)
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = "ProcessedSampleData";
                    cmd.Parameters.Add("@sample", System.Data.SqlDbType.VarChar);
                    cmd.Connection.Open();

                    try
                    {
                        foreach (var item in samples)
                        {
                            if (item.ForeignKeyMap.ContainsKey("Sample"))
                            {
                                cmd.Parameters["@sample"].Value = item.ForeignKeyMap["Sample"];
                                using (SqlDataReader reader = cmd.ExecuteReader())
                                {
                                    while (reader.Read())
                                    {
                                        ret.Add(reader.GetString(0), item.SampleId, reader.GetDouble(1));
                                    }
                                    reader.Close();
                                }
                            }
                        }
                    }
                    finally
                    {
                        cmd.Connection.Close();
                    }
                }
            }

            return ret;
        }

        /// <summary>
        /// Indicates whether or not the provider supports automapping of samples.
        /// </summary>
        public bool SupportsAutoMapping
        {
            get { return false; }
        }

        /// <summary>
        /// test the connectionstring
        /// </summary>
        /// <param name="user"></param>
        /// <param name="password"></param>
        public ConnectResult TestCredentials(string user, string password)
        {
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
            GmdDataProviderConfig cfg = ConfigurationObject as GmdDataProviderConfig;
            csb.DataSource = cfg.Server;
            csb.InitialCatalog = cfg.DataBase;
            csb.IntegratedSecurity = cfg.WindowsAuthentication;
            csb.UserID = user;
            csb.Password = password;

            if (TestCredentials(csb.ConnectionString))
            {
                return ConnectResult.Success;
            }
            else
            {
                return ConnectResult.Failed;
            }
        }

        /// <summary>
        /// The validation method of the dummy provider accepts each value for mappings with correct syntax 
        /// </summary>
        public SidLookUpResult Validate(SidMapping sim)
        {
            if (!ListForeignKey().All(x => sim.ForeignKeyMap.ContainsKey(x)))
            {
                return SidLookUpResult.UnsupportedMapping;
            }

            if (ListForeignKey().Any(x => string.IsNullOrEmpty(sim.ForeignKeyMap[x])))
            {
                return SidLookUpResult.UnsupportedMapping;
            }

            string exp = sim.ForeignKeyMap["Experiment"];
            //in real project proof whether the experiment exists in the data source 
            string sample = sim.ForeignKeyMap["Chromatogram"];

            //in real project proof whether the experiment exists in the experiment 
            return SidLookUpResult.Valid; //the dummy provider accepts each supported mapping }
        }
        #endregion interface IDataProvider

        #region helper methods
        /// <summary>
        /// Get's or set's the connection string.
        /// </summary>
        private string GetConnectionString(out bool success)
        {
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
            GmdDataProviderConfig cfg = ConfigurationObject as GmdDataProviderConfig;
            csb.DataSource = cfg.Server;
            csb.InitialCatalog = cfg.DataBase;
            csb.IntegratedSecurity = cfg.WindowsAuthentication;

            if (isAuthenticated)
            {
                csb.UserID = _UserName;
                csb.Password = _Password;
                success = true;
            }
            else
            {
                if (!cfg.WindowsAuthentication)
                {
                    AuthenticationRequestEventArgs a = new AuthenticationRequestEventArgs();
                    AuthenticationRequest(this, a);

                    if (a.Cancel)
                    {
                        success = false;
                    }
                    else
                    {
                        if (a.PersistCredentials)
                        {
                            _UserName = a.UserName;
                            _Password = a.Password;
                        }
                        csb.UserID = a.UserName;
                        csb.Password = a.Password;
                        isAuthenticated = true;
                        success = true;
                    }
                }
                else
                {
                    success = true;
                }
            }

            return csb.ConnectionString;
        }

        /// <summary>
        /// test the connectionstring
        /// </summary>
        /// <param name="ConnectionString"></param>
        /// <returns>true means 'fine', false refers to problem</returns>
        private bool TestCredentials(string ConnectionString)
        {
            using (SqlCommand cmd = new SqlCommand())
            using (cmd.Connection = new SqlConnection(ConnectionString))
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "select 1";
                cmd.Connection.Open();

                try
                {
                    return (cmd.ExecuteNonQuery() == 1);
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }
        }

        /// <summary>
        /// optional die ToString() Methode überschreiben.
        /// </summary>
        public override string ToString()
        {
            return this.ComponentName;
        }
        #endregion helper methods

        public string ConnectionString()
        {
            return ((GmdDataProviderConfig)ConfigurationObject).ConnectionString;
        }

        public System.Data.IDbConnection BuildConnection()
        {
            return new SqlConnection(((GmdDataProviderConfig)ConfigurationObject).ConnectionString);
        }
    }
}
